

#include <QSqlQuery>
#include <QSqlRecord>
#include <QVariant>
#include <QMessageBox>
#include "userdao.h"
#include "../entity/user.h"
#include <QDebug>

bool userLogin(MoneyUser *user) {
    if(!user)
        return false;

    qDebug() << "testing" << user->getLogin() << user->getPassword();

    QString strQuery = " SELECT id_user as id_user FROM user           "
                       " WHERE login = :login and password = :password ";
    QSqlQuery query;
    query.prepare(strQuery);
    query.bindValue(":login", user->getLogin());
    query.bindValue(":password", user->getPassword());

    query.exec();

    return (query.next());
}


bool createTableUser()
{
    QString strQuery = "create table user ( "
                                "id_user INTEGER,      "
                                "login VARCHAR(50),    "
                                "password VARCHAR(255),"
                                "prefix VARCHAR(3),    "
                                "primary key(id_user)  "
                        ")";
    QSqlQuery query;
    query.prepare(strQuery);

    return query.exec();
}

bool createAllUserTables()
{
    QString strQuery1 = "create table money (                          "
                                "id_money INTEGER,                     "
                                "id_user INTEGER,                      "
                                "amount DECIMAL(13,2),                 "
                                "dt_transaction Date,                  "
                                "description VARCHAR(255),             "
                                "timestamp VARCHAR(255),               "
                                "type_money INTEGER(1),                "
                                "primary key (id_money),               "
                                "foreign key (id_user) references      "
                                "user(id_user)                         "
                                "ON UPDATE CASCADE ON DELETE RESTRICT  "
                        ")";

    QString strQuery2 = "create table category (       "
                                "id_category INTEGER,      "
                                "id_user INTEGER,          "
                                "name VARCHAR(50),         "
                                "description VARCHAR(255), "
                                "primary key(id_category)  "
                                "foreign key (id_user) references      "
                                "user(id_user)                         "
                                "ON UPDATE CASCADE ON DELETE RESTRICT  "
                        ")";

    QString strQuery3 = " create table money_category (   "
                            "id_money INTEGER NOT NULL, "
                            "id_category INTEGER NOT NULL, "
                            "foreign key (id_money) references money(id_money),"
                            "foreign key (id_category) references category(id_category))";

    QString strQuery4 = " create table goal (                                    "
                                " id_goal INTEGER,                               "
                                " id_user INTEGER,                               "
                                " description VARCHAR(255),                      "
                                " dt_initial Date,                               "
                                " dt_final Date,                                 "
                                " target_value DECIMAL(13,2),                    "
                                " type_goal INTEGER(2),                          "
                                " primary key (id_goal),                         "
                                " foreign key (id_user) references user(id_user) "
                                " ON UPDATE CASCADE ON DELETE RESTRICT           "
                        ")";

    qDebug() << strQuery1;
    qDebug() << strQuery2;
    qDebug() << strQuery3;
    qDebug() << strQuery4;

    QSqlQuery query1;
    query1.prepare(strQuery1);

    QSqlQuery query2;
    query2.prepare(strQuery2);

    QSqlQuery query3;
    query3.prepare(strQuery3);

    QSqlQuery query4;
    query4.prepare(strQuery4);

    return query1.exec() && query2.exec() && query3.exec() && query4.exec();

}

void createSampleData(MoneyUser *user)
{
    qDebug() << __PRETTY_FUNCTION__;

    QStringList categories;
    categories << "Comida" << "Saude" << "Viagens" << "Livros";

    foreach(QString cat_name, categories) {
        QSqlQuery query;
        query.prepare(" INSERT into category (name, description, id_user) "
                      " values (:cat_name,'bla bla',:id_user) ");
        query.bindValue(":id_user", user->getId());
        query.bindValue(":cat_name", cat_name);
        query.exec();
    }
}

bool registerUser(MoneyUser *user)
{
    qDebug() << __PRETTY_FUNCTION__;

    if (user && !userLogin(user)) {

        QString strQuery = "INSERT INTO user (login, password, prefix) values (:login, :password, :prefix)";

        QSqlQuery query;
        query.prepare(strQuery);
        query.bindValue(":login", user->getLogin());
        query.bindValue(":password",user->getPassword());
        query.bindValue(":prefix", user->getPrefix());

        return query.exec();
    } else return false;

    return false;
}

MoneyUser* createStandardUser()
{
    qDebug() << __PRETTY_FUNCTION__;
    MoneyUser* admin_user  = new MoneyUser(STANDARD_LOGIN, STANDARD_PASSWORD, STANDARD_PREFIX);

    if (!userLogin(admin_user)) {
        createTableUser();
        createAllUserTables();


        QString strQuery = "INSERT INTO user (login, password, prefix) values (:login, :password, :prefix)";

        QSqlQuery query;
        query.prepare(strQuery);
        query.bindValue(":login", admin_user->getLogin());
        query.bindValue(":password", admin_user->getPassword());
        query.bindValue(":prefix", admin_user->getPrefix());

        query.exec();

        admin_user = getUser(admin_user);
        createSampleData(admin_user);
    }

    return admin_user;
}

MoneyUser* getUser(MoneyUser* incomplete_user) {
    if(!incomplete_user)
        return 0;

    QString strQuery = "SELECT id_user, login, password, prefix as id_user, login, password, prefix FROM user "
                       " WHERE login = :login and password = :password";
    QSqlQuery query;
    query.prepare(strQuery);
    query.bindValue(":login", incomplete_user->getLogin());
    query.bindValue(":password", incomplete_user->getPassword());

    query.exec();

    MoneyUser* user = new MoneyUser();

    if (query.next()) {
        user->setId(query.value(query.record().indexOf("id_user")).toInt());
        user->setLogin(query.value(query.record().indexOf("login")).toString());
        user->setPassword(query.value(query.record().indexOf("password")).toString());
        user->setPrefix(query.value(query.record().indexOf("prefix")).toString());
    } else return 0;

    return user;
}
